<?php

/**
 * 
 */
class Routes_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function fetchRoutes() {
        $sql = "SELECT * from routes order by route_id desc LIMIT 10";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchRoutesByUserId($user_id) {
        $sql = "SELECT count(*) as pocet,sum(kilometres) as kms from routes where user_id='$user_id'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function fetchRoutesByUser($value = '') {
        # code...
    }

    public function fetchBasicRoutesStats($value = '') {
        $sql = "select sum(n_patrol+n_police+n_accident+n_mobileradar) as rep,
		 				sum(kilometres) as km,
		 				sum(time_driven) as td,
		 				round(sum(avg_speed)/count(*),1) as avg 
		 					from routes";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function insert($user_id, $kilometres, $avg_speed, $time_driven, $s_latitude, $s_longitude, $e_latitude, $e_longitude, $s_street, $s_city, $s_state, $e_street, $e_city, $e_state) {
        $added = date('Y-m-d H:i:s');
        $sql = "INSERT INTO routes VALUES ('','$user_id',
											 '$added','$kilometres',
											 '$avg_speed','$time_driven',
											 '$s_latitude','$s_longitude',
											 '$e_latitude','$e_longitude',
											 '$s_street','$s_city','$s_state',
											 '$e_street','$e_city','$e_state',
											 '0','0','0','0')";
        $this->db->query($sql);
    }

    public function update_route_routes($rad, $hl, $neh, $mob, $user_id) {
        $sql = "UPDATE routes 
				SET n_police=$rad, 
				n_patrol=$hl,
				n_accident=$neh,
				n_mobileradar=$mob 
					WHERE user_id='$user_id' 
						order by route_id desc 
							LIMIT 1";
        $this->db->query($sql);
    }

}

?>